# diff_tables.py （更新版，包含 question 输出）

import os,sys
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
sys.path.insert(0, project_root)
import pandas as pd
from config import project_config
from query.extract_tables import extract_tables

def main():
    # 读取两个文件
    try:
        samples_df = pd.read_json(project_config.samples_path, lines=True, encoding='utf-8')
        results_df = pd.read_json(project_config.data_answer_path, lines=True, encoding='utf-8')
    except Exception as e:
        print(f"读取文件失败：{e}")
        return

    # 按 id 建立索引
    samples_df.set_index('id', inplace=True)
    results_df.set_index('id', inplace=True)

    # 合并 DataFrame，保留 question
    merged = samples_df[['question', 'sql']].join(results_df[['sql']], how='inner', rsuffix='_result')

    # 统计比较结果
    total = 0
    mismatch = 0
    mismatch_list = []

    for idx, row in merged.iterrows():
        total += 1
        expected_sql = row['sql']
        actual_sql = row['sql_result']
        question = row['question']
        # print(f"[DEBUG]id:{idx},question:{question}")  

        expected_tables = extract_tables(expected_sql)
        actual_tables = extract_tables(actual_sql)

        if expected_tables != actual_tables:
            mismatch += 1
            mismatch_list.append({
                'id': idx,
                'question': question,
                'expected_sql': expected_sql,
                'actual_sql': actual_sql,
                'expected_tables': ', '.join(sorted(expected_tables)),
                'actual_tables': ', '.join(sorted(actual_tables))
            })

    # 输出结果
    if mismatch_list:
        print("❌ 以下记录的SQL涉及的表不一致：")
        for item in mismatch_list:
            print(f"\nID: {item['id']}")
            print(f"  问题: {item['question']}  期望SQL表: {item['expected_tables']}  实际SQL表: {item['actual_tables']}")
            print(f"  期望SQL: {item['expected_sql']}")
            print(f"  实际SQL: {item['actual_sql']}")
    else:
        print("✅ 所有记录的SQL涉及的表均一致！")

    print(f"✅ 完成检查：共 {total} 条记录，其中 {mismatch} 条表名不一致。\n")

if __name__ == '__main__':
    main()